﻿USE [FiveLeagueTeamData]
GO
/****** Object:  Table [dbo].[LeagueTeamDataForReportPage]    Script Date: 10/27/2011 04:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LeagueTeamDataForReportPage](
	[TeamDataID] [uniqueidentifier] NOT NULL,
	[RoundID] [uniqueidentifier] NOT NULL,
	[TeamID] [uniqueidentifier] NOT NULL,
	[Ranking] [tinyint] NOT NULL,
	[Goal] [tinyint] NOT NULL,
	[Shot] [tinyint] NOT NULL,
	[ShotOnTarget] [tinyint] NOT NULL,
	[ShotOutTarget] [tinyint] NOT NULL,
	[BlockedShot] [tinyint] NOT NULL,
	[Corner] [tinyint] NOT NULL,
	[FreeKick] [tinyint] NOT NULL,
	[OutOfBounds] [tinyint] NOT NULL,
	[Foul] [tinyint] NOT NULL,
	[Offside] [tinyint] NOT NULL,
	[DivingSave] [tinyint] NOT NULL,
	[Trackling] [tinyint] NOT NULL,
	[BallControl] [tinyint] NOT NULL,
	[PassSuccess] [tinyint] NOT NULL,
	[PassMiddleSuccess] [tinyint] NOT NULL,
	[TacklingSuccess] [tinyint] NOT NULL,
	[HeadSuccess] [tinyint] NOT NULL,
	[YellowCard] [tinyint] NOT NULL,
	[RedCard] [tinyint] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_TeamDataForReportPage] PRIMARY KEY CLUSTERED 
(
	[TeamDataID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  UserDefinedFunction [dbo].[SPLIT]    Script Date: 10/27/2011 04:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SPLIT](@string VARCHAR(2000),@split VARCHAR(2))
RETURNS @result TABLE(strColumn VARCHAR(50))
AS
BEGIN
	WHILE(CHARINDEX(@split,@string) <> 0)
	BEGIN
		INSERT @result(strColumn) VALUES (SUBSTRING(@string,1,CHARINDEX(@split,@string)-1));
		SET @string = STUFF(@string,1,CHARINDEX(@split,@string), '');
	END
	INSERT @result(strColumn) VALUES(@string);
	RETURN;
END
GO
/****** Object:  Table [dbo].[LeagueMatchStats]    Script Date: 10/27/2011 04:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeagueMatchStats](
	[MatchStatsHtmlID] [uniqueidentifier] NOT NULL,
	[SeasonYear] [smallint] NOT NULL,
	[LeagueName] [varchar](50) NOT NULL,
	[RoundNumber] [tinyint] NOT NULL,
	[HomeTeam] [varchar](50) NOT NULL,
	[VisitingTeam] [varchar](50) NOT NULL,
	[MatchStatsUrl] [varchar](500) NOT NULL,
	[MatchStatsHtml] [varchar](max) NOT NULL,
	[MatchStatsJsonUrl] [varchar](500) NOT NULL,
	[MatchStatsJson] [varchar](max) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LeagueInfo]    Script Date: 10/27/2011 04:43:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeagueInfo](
	[LeagueID] [uniqueidentifier] NOT NULL,
	[LeagueName] [varchar](50) NOT NULL,
	[Note] [varchar](200) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_League] PRIMARY KEY CLUSTERED 
(
	[LeagueID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LeagueTeam]    Script Date: 10/27/2011 04:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LeagueTeam](
	[TeamID] [uniqueidentifier] NOT NULL,
	[LeagueID] [uniqueidentifier] NOT NULL,
	[TeamName] [nvarchar](50) NOT NULL,
	[Note] [nvarchar](100) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Team1] PRIMARY KEY CLUSTERED 
(
	[TeamID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[LeagueMatchStatsInsert]    Script Date: 10/27/2011 04:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LeagueMatchStatsInsert]
	-- Add the parameters for the stored procedure here
	@SeasonYear SMALLINT,	
	@LeagueName VARCHAR(50),
	@HomeTeam VARCHAR(50),
	@VisitingTeam VARCHAR(50),
	@MatchStatsUrl VARCHAR(500),
	@MatchStatsHtml VARCHAR(MAX),
	@MatchStatsJsonUrl VARCHAR(500),
	@MatchStatsJson VARCHAR(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @matchStatsHtmlID UNIQUEIDENTIFIER;
	
	SELECT @matchStatsHtmlID = MatchStatsHtmlID 
	FROM LeagueMatchStats 
	WHERE SeasonYear = @SeasonYear 
		AND LeagueName = @LeagueName 
		AND HomeTeam = @HomeTeam
		AND VisitingTeam = @VisitingTeam;
	
	IF @matchStatsHtmlID IS NULL
	BEGIN
		INSERT INTO LeagueMatchStats(SeasonYear, LeagueName, HomeTeam, VisitingTeam,
			MatchStatsUrl, MatchStatsHtml, MatchStatsJsonUrl, MatchStatsJson) 
		VALUES(@SeasonYear, @LeagueName, @HomeTeam, @VisitingTeam,
			@MatchStatsUrl, @MatchStatsHtml, @MatchStatsJsonUrl, @MatchStatsJson)			
	END ELSE BEGIN
		UPDATE LeagueMatchStats SET SeasonYear = @SeasonYear, LeagueName = @LeagueName,
			HomeTeam = @HomeTeam, VisitingTeam = @VisitingTeam, MatchStatsUrl = @MatchStatsUrl,
			MatchStatsHtml = @MatchStatsHtml, MatchStatsJsonUrl = @MatchStatsJsonUrl,
			MatchStatsJson = @MatchStatsJson, UpdateTime = GETDATE()
		WHERE MatchStatsHtmlID = @matchStatsHtmlID
	END
		
END
GO
/****** Object:  StoredProcedure [dbo].[LeagueInfoInsert]    Script Date: 10/27/2011 04:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LeagueInfoInsert] 
	-- Add the parameters for the stored procedure here
	@LeagueName VARCHAR(50),
	@Note VARCHAR(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @leagueID UNIQUEIDENTIFIER;
	SELECT @leagueID = LeagueID FROM LeagueInfo WHERE LeagueName = @LeagueName;
	IF @leagueID IS NULL
	BEGIN
		SELECT @leagueID = NEWID();
		INSERT INTO LeagueInfo(LeagueID, LeagueName, Note) VALUES(@leagueID, @LeagueName, @Note);
	END
	SELECT @leagueID AS LeagueID;
END
GO
/****** Object:  Table [dbo].[LeagueTeamRanking]    Script Date: 10/27/2011 04:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LeagueTeamRanking](
	[RankingID] [uniqueidentifier] NOT NULL,
	[LeagueID] [uniqueidentifier] NOT NULL,
	[RoundNumber] [uniqueidentifier] NOT NULL,
	[TeamID] [uniqueidentifier] NOT NULL,
	[TeamRanking] [tinyint] NOT NULL,
	[TeamMatchCount] [tinyint] NOT NULL,
	[MatchWin] [tinyint] NOT NULL,
	[MatchDraw] [tinyint] NOT NULL,
	[MatchLose] [tinyint] NOT NULL,
	[Goal] [tinyint] NOT NULL,
	[Lose] [tinyint] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[DataIndex] [int] NOT NULL,
 CONSTRAINT [PK_TeamRanking] PRIMARY KEY CLUSTERED 
(
	[RankingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[LeagueTeamNameBatchInsert]    Script Date: 10/27/2011 04:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LeagueTeamNameBatchInsert] 
	-- Add the parameters for the stored procedure here
	@LeagueID VARCHAR(50),
	@TeamNameList VARCHAR(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    
	IF CURSOR_STATUS('global','myCursor') = 1
	BEGIN
		CLOSE myCursor;
		DEALLOCATE myCursor;
	END
	
	DECLARE @teamName VARCHAR(50);
	DECLARE @teamID UNIQUEIDENTIFIER;
	
	DECLARE myCursor CURSOR FOR
		SELECT * 
		FROM SPLIT(@TeamNameList,',')
	OPEN myCursor;
	
	FETCH NEXT FROM myCursor INTO @teamName;
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @teamID = TeamID 
		FROM LeagueTeam 
		WHERE TeamName = @teamName AND LeagueID = @LeagueID;
		
		IF @teamID IS NULL 
		BEGIN
			INSERT INTO LeagueTeam(LeagueID, TeamName) VALUES(@LeagueID,@teamName);
		END
		
		FETCH NEXT FROM myCursor INTO @teamName;	
	END
	
	SELECT TeamID, TeamName 
	FROM LeagueTeam 
	WHERE TeamName IN (SELECT * FROM SPLIT(@TeamNameList,','))
END
GO
/****** Object:  Table [dbo].[LeagueMatch]    Script Date: 10/27/2011 04:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LeagueMatch](
	[MatchID] [uniqueidentifier] NOT NULL,
	[LeagueID] [uniqueidentifier] NOT NULL,
	[HomeTeamID] [uniqueidentifier] NOT NULL,
	[VisitingTeamID] [uniqueidentifier] NOT NULL,
	[SeasonYear] [smallint] NOT NULL,
	[RoundNumber] [tinyint] NOT NULL,
	[MatchDateTime] [datetime] NOT NULL,
	[Stadium] [varchar](100) NULL,
	[Weather] [varchar](100) NULL,
	[Note] [nvarchar](100) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_LeagueMatch] PRIMARY KEY CLUSTERED 
(
	[MatchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LeagueTeamData]    Script Date: 10/27/2011 04:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LeagueTeamData](
	[TeamDataID] [uniqueidentifier] NOT NULL,
	[MatchID] [uniqueidentifier] NOT NULL,
	[TeamID] [uniqueidentifier] NOT NULL,
	[IsAtHome] [bit] NOT NULL,
	[Goal] [tinyint] NOT NULL,
	[Shot] [tinyint] NOT NULL,
	[ShotOnTarget] [tinyint] NOT NULL,
	[BlockedShot] [tinyint] NOT NULL,
	[Corner] [tinyint] NOT NULL,
	[FreeKick] [tinyint] NOT NULL,
	[OutOfBounds] [tinyint] NOT NULL,
	[Foul] [tinyint] NOT NULL,
	[Offside] [tinyint] NOT NULL,
	[BallControl] [decimal](5, 2) NOT NULL,
	[PassSuccess] [decimal](5, 2) NOT NULL,
	[PassMiddleSuccess] [decimal](5, 2) NOT NULL,
	[TacklingSuccess] [decimal](5, 2) NOT NULL,
	[HeadSuccess] [decimal](5, 2) NOT NULL,
	[YellowCard] [tinyint] NOT NULL,
	[RedCard] [tinyint] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[DataIndex] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_TeamData] PRIMARY KEY CLUSTERED 
(
	[TeamDataID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[LeagueMatchInsert]    Script Date: 10/27/2011 04:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LeagueMatchInsert]
	-- Add the parameters for the stored procedure here
	@SeasonYear SMALLINT,
	@LeagueID VARCHAR(50),
	@HomeTeamID VARCHAR(50),
	@VisitingTeamID VARCHAR(50),
	@RoundNumber TINYINT,
	@MatchDateTime DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @matchID UNIQUEIDENTIFIER;
    
	SELECT @matchID = m.MatchID 
	FROM LeagueMatch m	
	WHERE m.SeasonYear = @SeasonYear
		AND m.LeagueID = @LeagueID
		AND m.RoundNumber = @RoundNumber
		AND m.HomeTeamID = @HomeTeamID
		AND m.VisitingTeamID = @VisitingTeamID
	
	IF @matchID IS NULL BEGIN
		SELECT @matchID = NEWID();
		INSERT INTO LeagueMatch(SeasonYear,MatchID,LeagueID,HomeTeamID,VisitingTeamID,RoundNumber,MatchDateTime) 
			VALUES(@SeasonYear,@matchID,@LeagueID,@HomeTeamID,@VisitingTeamID,@RoundNumber,@MatchDateTime)
	END
	SELECT @matchID as MatchID;
END
GO
/****** Object:  StoredProcedure [dbo].[LeagueTeamDataInsert]    Script Date: 10/27/2011 04:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[LeagueTeamDataInsert]
	-- Add the parameters for the stored procedure here
	@MatchID UNIQUEIDENTIFIER,
	@TeamID UNIQUEIDENTIFIER,
	@IsAtHome BIT,
	@Goal TINYINT,
	@Shot TINYINT,
	@ShotOnTarget TINYINT,
	@BlockedShot TINYINT,
	@Corner TINYINT,
	@FreeKick TINYINT,
	@OutOfBounds TINYINT,
	@Foul TINYINT,
	@Offside TINYINT,
	@BallControl DECIMAL(5,2),
	@PassSuccess DECIMAL(5,2),
	@PassMiddleSuccess DECIMAL(5,2),
	@TacklingSuccess DECIMAL(5,2),
	@YellowCard TINYINT,
	@RedCard TINYINT	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @teamDataID UNIQUEIDENTIFIER;
	
	SELECT @teamDataID = TeamDataID FROM LeagueTeamData 
	WHERE MatchID = @MatchID AND TeamID = @TeamID;
		
	IF @teamDataID IS NULL BEGIN
		SELECT @teamDataID = NEWID();
		INSERT INTO LeagueTeamData(TeamDataID, MatchID, TeamID, IsAtHome, Goal, Shot, ShotOnTarget, 
			BlockedShot, Corner, FreeKick, OutOfBounds, Foul, Offside, BallControl, 
			PassSuccess, PassMiddleSuccess,	TacklingSuccess, YellowCard, RedCard)
		VALUES(@teamDataID, @MatchID, @TeamID, @IsAtHome, @Goal, @Shot, @ShotOnTarget, @BlockedShot, 
			@Corner, @FreeKick, @OutOfBounds, @Foul, @Offside, @BallControl, @PassSuccess, 
			@PassMiddleSuccess,	@TacklingSuccess, @YellowCard, @RedCard);
	END ELSE BEGIN 
		UPDATE LeagueTeamData SET MatchID=@MatchID, TeamID=@TeamID, IsAtHome=@IsAtHome, Goal=@Goal, 
			Shot=@Shot,	ShotOnTarget=@ShotOnTarget, BlockedShot=@BlockedShot, Corner=@Corner,
			FreeKick=@FreeKick, OutOfBounds=@OutOfBounds, Foul=@Foul, Offside=@Offside,
			BallControl=@BallControl, PassSuccess=@PassSuccess, PassMiddleSuccess=@PassMiddleSuccess,
			TacklingSuccess=@TacklingSuccess, YellowCard=@YellowCard, RedCard=@RedCard, UpdateTime=GETDATE()  
		WHERE TeamDataID = @teamDataID;
	END
	
    -- Insert statements for procedure here
	SELECT @teamDataID AS TeamDataID;
END
GO
/****** Object:  UserDefinedFunction [dbo].[Fun_SeasonMatchList]    Script Date: 10/27/2011 04:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[Fun_SeasonMatchList]
(	
	-- Add the parameters for the function here
	@leagueID uniqueidentifier,
	@seasonYear smallint
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT match.MatchDateTime, league.LeagueName, match.RoundNumber, homeTeam.TeamName AS HomeTeam
		, visitingTeam.TeamName AS VisitingTeam, homeTeamData.Goal AS HomeGoal
		, visitingTeamData.Goal AS VisitingGoal
	FROM LeagueMatch match
	LEFT JOIN LeagueInfo league ON match.LeagueID = league.LeagueID
	LEFT JOIN LeagueTeam homeTeam ON match.HomeTeamID = homeTeam.TeamID
	LEFT JOIN LeagueTeam visitingTeam ON match.VisitingTeamID = visitingTeam.TeamID
	LEFT JOIN LeagueTeamData homeTeamData 
		ON match.MatchID = homeTeamData.MatchID AND homeTeam.TeamID = homeTeamData.TeamID
	LEFT JOIN LeagueTeamData visitingTeamData 
		ON match.MatchID = visitingTeamData.MatchID AND visitingTeam.TeamID = visitingTeamData.TeamID
	WHERE league.LeagueID=@leagueID AND match.SeasonYear = @seasonYear
)
GO
/****** Object:  StoredProcedure [dbo].[DataClear]    Script Date: 10/27/2011 04:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE  [dbo].[DataClear]
	-- Add the parameters for the stored procedure here	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM LeagueTeamRanking;
	DELETE FROM LeagueMatch;
	DELETE FROM LeagueTeamData;
	DELETE FROM LeagueTeam;
	DELETE FROM LeagueInfo;
END
GO
/****** Object:  Default [DF_League_CreateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueInfo] ADD  CONSTRAINT [DF_League_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  Default [DF_League_UpdateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueInfo] ADD  CONSTRAINT [DF_League_UpdateTime]  DEFAULT (getdate()) FOR [UpdateTime]
GO
/****** Object:  Default [DF_League_IsDeleted]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueInfo] ADD  CONSTRAINT [DF_League_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  Default [DF_LeagueMatch_Season]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch] ADD  CONSTRAINT [DF_LeagueMatch_Season]  DEFAULT ((0)) FOR [SeasonYear]
GO
/****** Object:  Default [DF_LeagueMatch_RoundNumber]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch] ADD  CONSTRAINT [DF_LeagueMatch_RoundNumber]  DEFAULT ((0)) FOR [RoundNumber]
GO
/****** Object:  Default [DF_LeagueMatch_MatchDateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch] ADD  CONSTRAINT [DF_LeagueMatch_MatchDateTime]  DEFAULT (getdate()) FOR [MatchDateTime]
GO
/****** Object:  Default [DF_LeagueMatch_CreateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch] ADD  CONSTRAINT [DF_LeagueMatch_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  Default [DF_LeagueMatch_UpdateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch] ADD  CONSTRAINT [DF_LeagueMatch_UpdateTime]  DEFAULT (getdate()) FOR [UpdateTime]
GO
/****** Object:  Default [DF_LeagueMatch_IsDeleted]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch] ADD  CONSTRAINT [DF_LeagueMatch_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  Default [DF_LeagueMatchStats_MatchStatsHtmlID]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_MatchStatsHtmlID]  DEFAULT (newid()) FOR [MatchStatsHtmlID]
GO
/****** Object:  Default [DF_LeagueMatchStats_SeasonYear]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_SeasonYear]  DEFAULT ((0)) FOR [SeasonYear]
GO
/****** Object:  Default [DF_LeagueMatchStats_LeagueName]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_LeagueName]  DEFAULT ('') FOR [LeagueName]
GO
/****** Object:  Default [DF_LeagueMatchStats_RoundNumber]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_RoundNumber]  DEFAULT ((0)) FOR [RoundNumber]
GO
/****** Object:  Default [DF_LeagueMatchStats_HomeTeam]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_HomeTeam]  DEFAULT ('') FOR [HomeTeam]
GO
/****** Object:  Default [DF_LeagueMatchStats_VisitingTeam]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_VisitingTeam]  DEFAULT ('') FOR [VisitingTeam]
GO
/****** Object:  Default [DF_LeagueMatchStats_MatchStatsUrl]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_MatchStatsUrl]  DEFAULT ('') FOR [MatchStatsUrl]
GO
/****** Object:  Default [DF_LeagueMatchStats_MatchStatsHtml]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_MatchStatsHtml]  DEFAULT ('') FOR [MatchStatsHtml]
GO
/****** Object:  Default [DF_LeagueMatchStats_MatchStatsJsonUrl]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_MatchStatsJsonUrl]  DEFAULT ('') FOR [MatchStatsJsonUrl]
GO
/****** Object:  Default [DF_LeagueMatchStats_MatchStatsJSON]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_MatchStatsJSON]  DEFAULT ('') FOR [MatchStatsJson]
GO
/****** Object:  Default [DF_LeagueMatchStats_CreateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  Default [DF_LeagueMatchStats_UpdateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_UpdateTime]  DEFAULT (getdate()) FOR [UpdateTime]
GO
/****** Object:  Default [DF_LeagueMatchStats_IsDeleted]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatchStats] ADD  CONSTRAINT [DF_LeagueMatchStats_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  Default [DF_Team1_TeamID]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeam] ADD  CONSTRAINT [DF_Team1_TeamID]  DEFAULT (newid()) FOR [TeamID]
GO
/****** Object:  Default [DF_Team1_CreateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeam] ADD  CONSTRAINT [DF_Team1_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  Default [DF_Team1_UpdateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeam] ADD  CONSTRAINT [DF_Team1_UpdateTime]  DEFAULT (getdate()) FOR [UpdateTime]
GO
/****** Object:  Default [DF_Team1_IsDeleted]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeam] ADD  CONSTRAINT [DF_Team1_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  Default [DF_LeagueTeamData_IsAtHome]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_LeagueTeamData_IsAtHome]  DEFAULT ((1)) FOR [IsAtHome]
GO
/****** Object:  Default [DF_TeamData_Goal]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_Goal]  DEFAULT ((0)) FOR [Goal]
GO
/****** Object:  Default [DF_TeamData_Shot]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_Shot]  DEFAULT ((0)) FOR [Shot]
GO
/****** Object:  Default [DF_TeamData_ShotOnTarget]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_ShotOnTarget]  DEFAULT ((0)) FOR [ShotOnTarget]
GO
/****** Object:  Default [DF_TeamData_DivingSave]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_DivingSave]  DEFAULT ((0)) FOR [BlockedShot]
GO
/****** Object:  Default [DF_TeamData_Corner]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_Corner]  DEFAULT ((0)) FOR [Corner]
GO
/****** Object:  Default [DF_TeamData_FreeKick]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_FreeKick]  DEFAULT ((0)) FOR [FreeKick]
GO
/****** Object:  Default [DF_TeamData_OutOfBounds]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_OutOfBounds]  DEFAULT ((0)) FOR [OutOfBounds]
GO
/****** Object:  Default [DF_TeamData_Foul]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_Foul]  DEFAULT ((0)) FOR [Foul]
GO
/****** Object:  Default [DF_TeamData_Offside]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_Offside]  DEFAULT ((0)) FOR [Offside]
GO
/****** Object:  Default [DF_TeamData_BallControl]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_BallControl]  DEFAULT ((0)) FOR [BallControl]
GO
/****** Object:  Default [DF_TeamData_PassSuccess]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_PassSuccess]  DEFAULT ((0)) FOR [PassSuccess]
GO
/****** Object:  Default [DF_TeamData_PassMiddleSuccess]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_PassMiddleSuccess]  DEFAULT ((0)) FOR [PassMiddleSuccess]
GO
/****** Object:  Default [DF_TeamData_TracklingSuccess]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_TracklingSuccess]  DEFAULT ((0)) FOR [TacklingSuccess]
GO
/****** Object:  Default [DF_TeamData_HeadSuccess]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_HeadSuccess]  DEFAULT ((0)) FOR [HeadSuccess]
GO
/****** Object:  Default [DF_TeamData_YellowCard]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_YellowCard]  DEFAULT ((0)) FOR [YellowCard]
GO
/****** Object:  Default [DF_TeamData_RedCard]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_RedCard]  DEFAULT ((0)) FOR [RedCard]
GO
/****** Object:  Default [DF_TeamData_CreateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  Default [DF_TeamData_UpdateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_UpdateTime]  DEFAULT (getdate()) FOR [UpdateTime]
GO
/****** Object:  Default [DF_TeamData_IsDeleted]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData] ADD  CONSTRAINT [DF_TeamData_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  Default [DF_TeamRanking_CreateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamRanking] ADD  CONSTRAINT [DF_TeamRanking_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  Default [DF_TeamRanking_UpdateTime]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamRanking] ADD  CONSTRAINT [DF_TeamRanking_UpdateTime]  DEFAULT (getdate()) FOR [UpdateTime]
GO
/****** Object:  Default [DF_TeamRanking_IsDeleted]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamRanking] ADD  CONSTRAINT [DF_TeamRanking_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
/****** Object:  ForeignKey [FK_LeagueMatch_LeagueInfo]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch]  WITH CHECK ADD  CONSTRAINT [FK_LeagueMatch_LeagueInfo] FOREIGN KEY([LeagueID])
REFERENCES [dbo].[LeagueInfo] ([LeagueID])
GO
ALTER TABLE [dbo].[LeagueMatch] CHECK CONSTRAINT [FK_LeagueMatch_LeagueInfo]
GO
/****** Object:  ForeignKey [FK_LeagueMatch_LeagueTeam]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch]  WITH CHECK ADD  CONSTRAINT [FK_LeagueMatch_LeagueTeam] FOREIGN KEY([HomeTeamID])
REFERENCES [dbo].[LeagueTeam] ([TeamID])
GO
ALTER TABLE [dbo].[LeagueMatch] CHECK CONSTRAINT [FK_LeagueMatch_LeagueTeam]
GO
/****** Object:  ForeignKey [FK_LeagueMatch_LeagueTeam1]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueMatch]  WITH CHECK ADD  CONSTRAINT [FK_LeagueMatch_LeagueTeam1] FOREIGN KEY([VisitingTeamID])
REFERENCES [dbo].[LeagueTeam] ([TeamID])
GO
ALTER TABLE [dbo].[LeagueMatch] CHECK CONSTRAINT [FK_LeagueMatch_LeagueTeam1]
GO
/****** Object:  ForeignKey [FK_LeagueTeam_LeagueInfo]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeam]  WITH CHECK ADD  CONSTRAINT [FK_LeagueTeam_LeagueInfo] FOREIGN KEY([LeagueID])
REFERENCES [dbo].[LeagueInfo] ([LeagueID])
GO
ALTER TABLE [dbo].[LeagueTeam] CHECK CONSTRAINT [FK_LeagueTeam_LeagueInfo]
GO
/****** Object:  ForeignKey [FK_TeamData_LeagueMatch]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData]  WITH CHECK ADD  CONSTRAINT [FK_TeamData_LeagueMatch] FOREIGN KEY([MatchID])
REFERENCES [dbo].[LeagueMatch] ([MatchID])
GO
ALTER TABLE [dbo].[LeagueTeamData] CHECK CONSTRAINT [FK_TeamData_LeagueMatch]
GO
/****** Object:  ForeignKey [FK_TeamData_LeagueTeam]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamData]  WITH CHECK ADD  CONSTRAINT [FK_TeamData_LeagueTeam] FOREIGN KEY([TeamID])
REFERENCES [dbo].[LeagueTeam] ([TeamID])
GO
ALTER TABLE [dbo].[LeagueTeamData] CHECK CONSTRAINT [FK_TeamData_LeagueTeam]
GO
/****** Object:  ForeignKey [FK_TeamRanking_LeagueInfo]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamRanking]  WITH CHECK ADD  CONSTRAINT [FK_TeamRanking_LeagueInfo] FOREIGN KEY([LeagueID])
REFERENCES [dbo].[LeagueInfo] ([LeagueID])
GO
ALTER TABLE [dbo].[LeagueTeamRanking] CHECK CONSTRAINT [FK_TeamRanking_LeagueInfo]
GO
/****** Object:  ForeignKey [FK_TeamRanking_LeagueTeam]    Script Date: 10/27/2011 04:43:14 ******/
ALTER TABLE [dbo].[LeagueTeamRanking]  WITH CHECK ADD  CONSTRAINT [FK_TeamRanking_LeagueTeam] FOREIGN KEY([TeamID])
REFERENCES [dbo].[LeagueTeam] ([TeamID])
GO
ALTER TABLE [dbo].[LeagueTeamRanking] CHECK CONSTRAINT [FK_TeamRanking_LeagueTeam]
GO
/****** Object:  UserDefinedTableType [dbo].[DataCollectionType]    Script Date: 10/27/2011 04:43:22 ******/
CREATE TYPE [dbo].[DataCollectionType] AS TABLE(
	[TeamDataID] [uniqueidentifier] NOT NULL,
	[RoundID] [uniqueidentifier] NOT NULL,
	[TeamDataName] [varchar](1) NOT NULL,
	[Goal] [tinyint] NOT NULL,
	[Shot] [tinyint] NOT NULL,
	[ShotOnTarget] [tinyint] NOT NULL,
	[ShotOutTarget] [tinyint] NOT NULL,
	[BlockedShot] [tinyint] NOT NULL,
	[Corner] [tinyint] NOT NULL,
	[FreeKick] [tinyint] NOT NULL,
	[OutOfBounds] [tinyint] NOT NULL,
	[Foul] [tinyint] NOT NULL,
	[Offside] [tinyint] NOT NULL,
	[DivingSave] [tinyint] NOT NULL,
	[Trackling] [tinyint] NOT NULL,
	[YellowCard] [tinyint] NOT NULL,
	[RedCard] [tinyint] NOT NULL,
	[BallControl] [decimal](5, 2) NOT NULL,
	[PassSuccess] [decimal](5, 2) NOT NULL,
	[PassMiddleSuccess] [decimal](5, 2) NOT NULL,
	[TracklingSuccess] [decimal](5, 2) NOT NULL,
	[HeadSuccess] [decimal](5, 2) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	PRIMARY KEY CLUSTERED 
(
	[TeamDataID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
